package com.scalar.chiptrack.finance.parser;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Arrays;
import java.util.List;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.AssemblyLot;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.finance.CostCodeInfo;
import com.scalar.chiptrack.finance.dao.CostCodeSetupDAO;
import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;

import jxl.Cell;
import jxl.Workbook;
import jxl.Sheet;
import jxl.CellType;

/**
 * User: ChandraSekhar
 * Date: Jun 1, 2007
 * Time: 1:05:00 PM
 */
public class CostCodeSetupParser extends JobHandler implements AdaptorConstants
{
    private static final String LINE_SEPARATOR = System.getProperty("line.separator");

    private Logger m_parserLogger       = null;
    private String m_dataOutputDir      = null;

    public  CostCodeSetupParser() throws ScalarException
    {                   
        try
        {
            m_dataOutputDir = ConfigManager.getCostCodePortingDirectory();

            AdaptorUtils.makeAllAdaptorDirs( m_dataOutputDir );
            m_parserLogger = AdaptorUtils.getDataParserLogger( m_dataOutputDir,"CostCode" );
            CostCodeSetupDAO.setLogger( m_parserLogger );
        }
        catch ( Exception se )
        {
            log( "ERROR!! ERROR!! ERROR!! Exception while getting output directory details from database, Exception: " + se );
        }
    }

    public static void main( String args[] ) throws Exception
    {
        try
        {
            new CostCodeSetupParser().execute();
        }
        catch( Exception e )
        {
            throw e;
        }
    }

    public void execute() throws Exception
    {
        AdaptorUtils.printHeader( "COST DETAILS Uploading", CONSTANT_PARSER);

        File doneDestDir = new File( m_dataOutputDir + "/done" );

        File costcodeDataoutDir = new File( m_dataOutputDir );

        File[] fileList = costcodeDataoutDir.listFiles();
        if ( ( fileList == null ) || ( fileList.length <= 3 ) )
        {
            log( "No file found in directory: " + costcodeDataoutDir );
            throw new ScalarException( "\n+-----------No file found in directory " + costcodeDataoutDir );
        }

        DBTransaction dbTransaction = null;
        ArrayList successFileList   = new ArrayList();
        boolean success             = true;

        for( int i = 0; i < fileList.length; i++ )
        {
            if ( fileList[i].isDirectory() == true )
            {
                continue;
            }

            try
            {
                // Step 1
                // Parse Files to get Object List
                if ( fileList[i].getName().startsWith( "Cost_Codes" ) )
                {
                    int year = Integer.parseInt( StringUtils.formatDateToString(new Date(), "yyyy") );
                    int Qtr  = Integer.parseInt( DateUtil.getCurrentQuarter() );

                    ArrayList<CostCodeInfo> costCodesList = parseCostCodeReport( fileList[i].getAbsolutePath(), "Q"+ DateUtil.getCurrentQuarter() );

                    try
                    {
                        if( costCodesList != null && costCodesList.size() > 0 )
                        {
                              CostCodeSetupDAO.saveCostCodeDetails( dbTransaction, costCodesList, year, Qtr );
                        }
                    }
                    catch( Exception se )
                    {
                        success = false;
                        se.printStackTrace();
                        log( "ERROR!! ERROR!! ERROR!! Could not update Cost Code information into database: " + se );
                        throw se;
                    }
                    successFileList.add( fileList[ i ] );
                }
                if ( fileList[i].getName().startsWith( "Unit_Cost_Deatil" ) )
                {

                    ArrayList<CostCodeInfo> costList = parseUnitCostReport( fileList[i].getAbsolutePath() );

                    try
                    {
                        if( costList != null && costList.size() > 0 )
                        {
                              CostCodeSetupDAO.saveCostDetails( dbTransaction, costList );
                        }
                    }
                    catch( Exception se )
                    {
                        success = false;
                        se.printStackTrace();
                        log( "ERROR!! ERROR!! ERROR!! Could not update Cost Code information into database: " + se );
                        throw se;
                    }
                    successFileList.add( fileList[ i ] );
                }
            }
            catch ( Exception  e )
            {
                success = false;
                log( "Exception while parsing and storing file: " + fileList[i].getAbsolutePath() + "\nException: " + e );
                e.printStackTrace();
                throw e;
            }
        }

        if( success == true )
        {
            System.out.println( "SUCCESSFULLY UPDATED IN THE DATABASE" );
        }
         // Move the successful files to 'done' directory
        if ( success == true )
        {
            AdaptorUtils.moveFilesToDone( successFileList, doneDestDir);
        }
   } // end of execute method.

    /**
     * @param fileName
     * @return
     * @throws Exception
     */
    private ArrayList<CostCodeInfo> parseCostCodeReport(String fileName, String quater ) throws Exception
    {
        ArrayList<CostCodeInfo> costCodesList = new ArrayList<CostCodeInfo>();
        CostCodeInfo costCodeInfo = null;
        String checkValue = null;

        if (fileName == null)
        {
            throw new ScalarException("Filename argument is null");
        }

        try
        {
            log("\nFound " + fileName);
            log("Parsing the file for Cost Codes Report...");
            System.out.println("\nFound " + fileName);
            System.out.println("Parsing the file for Cost Codes Report...");

            Workbook workbook = Workbook.getWorkbook( new FileInputStream( fileName ) );
            Sheet sheet = workbook.getSheet( quater );

            Cell[] row = null;
            Cell[] row3 = sheet.getRow( 3 );

            String[] tempArray = { "COST","ASSY", "BASE", "PKG", "PIN", "Package", "wafer cost", "die cost",
            "Assembled device", "Tested Device", "FG device", "FG TR Device"};
            List requieredFiledsList = Arrays.asList( tempArray );
            ArrayList sheetFieldsList = new ArrayList();
            int rowLength = row3.length;
                        
            for ( int i = 0; i < rowLength; i++ )
            {
               if ( row3[i].getContents() != null && row3[i].getContents().trim().length() > 0 )
               {
                   sheetFieldsList.add( row3[i].getContents().trim() );
               }
               else
               {
                   sheetFieldsList.add("");
               }
            }

            for ( int rowCount = 4; rowCount < sheet.getRows(); rowCount++ )
            {
                row = sheet.getRow( rowCount );

                costCodeInfo = new CostCodeInfo();

                for ( int i = 0; i < row.length; i++ )
                {
                    int index = requieredFiledsList.indexOf( sheetFieldsList.get(i) );

                    switch( index )
                    {
                        case 0:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setCostCode( null );
                            }
                            else
                            {
                                costCodeInfo.setCostCode( row[i].getContents().trim() );
                            }
                           break;
                        case 1:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                               costCodeInfo.setAssy( null );
                            }
                            else
                            {
                               costCodeInfo.setAssy( row[i].getContents().trim() ) ;
                            }
                            break;
                        case 2:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setBase(null );
                            }
                            else
                            {
                                costCodeInfo.setBase( row[i].getContents().trim() );
                            }
                            break;
                        case 3:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setPkg( null );
                            }
                            else
                            {
                                costCodeInfo.setPkg( row[i].getContents().trim() );
                            }
                            break;
                        case 4:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setPin( null );
                            }
                            else
                            {
                                costCodeInfo.setPin( row[i].getContents().trim() );
                            }
                            break;
                        case 5:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setPkgDesc( null );
                            }
                            else
                            {
                                costCodeInfo.setPkgDesc( row[i].getContents().trim() );
                            }
                            break;
                        case 6:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setWaferCost( 0 );
                            }
                            else
                            {
                                costCodeInfo.setWaferCost( Double.parseDouble(row[i].getContents().trim()) );
                            }
                            break;
                        case 7:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setDieCost( 0 );
                            }
                            else
                            {
                                costCodeInfo.setDieCost( Double.parseDouble( row[i].getContents().trim() ) );
                            }
                            break;
                        case 8:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setAssyCost( 0 );
                            }
                            else
                            {
                                costCodeInfo.setAssyCost( Double.parseDouble( row[i].getContents().trim() ) );
                            }
                            break;
                        case 9:
                             if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setTestCost( 0 );
                            }
                            else
                            {
                                costCodeInfo.setTestCost( Double.parseDouble( row[i].getContents().trim() ) );
                            }
                            break;
                        case 10:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setFgCost( 0 );
                            }
                            else
                            {
                                costCodeInfo.setFgCost( Double.parseDouble( row[i].getContents().trim() ) ) ;
                            }
                            break;
                        case 11:
                            if ( row[i].getType().equals(CellType.EMPTY) )
                            {
                                costCodeInfo.setTrCost( 0 );
                            }
                            else
                            {
                                costCodeInfo.setTrCost( Double.parseDouble( row[i].getContents().trim() ) );
                            }
                            break;
                        default:
                            break;
                    }
                }
                if ( costCodeInfo.getCostCode() != null && costCodeInfo.getCostCode().length() > 0  && "COST".equalsIgnoreCase(costCodeInfo.getCostCode() ) == false )
                {
					costCodesList.add( costCodeInfo );
                }
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Exception " + e);
        }

        return costCodesList;
    }

    private ArrayList<CostCodeInfo> parseUnitCostReport(String fileName ) throws Exception
    {
        ArrayList<CostCodeInfo> costList = new ArrayList<CostCodeInfo>();

        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result     = null;
        CostCodeInfo costInfo  = null;
        String partNumber       = null;
        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile, "Product Details Report" );

            ((XLSParserImpl)parser).setHeaderRowIdx(2);
            ((XLSParserImpl)parser).setDataStartRowIdx(3);

            log( "\nFound " + fileName );
            log( "\nParsing the file for Unit Cost Info Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for Unit Cost Info Report..." );

            result = parser.parse();

            while ( result.next() )
            {
                partNumber      = result.getString("Mkt PartNumber");

                if ( partNumber != null && partNumber.trim().length() > 0 )
                {
                      costInfo = new CostCodeInfo();

                      costInfo.setPartNumber( partNumber );
                      costInfo.setCostAccount(result.getString("Cost Account"));
                      costInfo.setVaDie( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("VA_die") ) ? "0": result.getString("VA_die") ) );
                      costInfo.setVaEProm( Double.parseDouble( "-".equalsIgnoreCase(result.getString("VA_eprom")) ? "0" : result.getString("VA_eprom") ) );
                      costInfo.setVaBG( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("VA_BG") ) ? "0" : result.getString("VA_BG") ) );
                      costInfo.setVaDieFrt( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("VA_diefrt") ) ? "0" : result.getString("VA_diefrt") ) );
                      costInfo.setVaAssy( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("VA_assy")) ? "0" : result.getString("VA_assy") ) );
                      costInfo.setVaAssyFrt( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("VA_assyfrt") ) ? "0" : result.getString("VA_assyfrt") ) );
                      costInfo.setVaTest( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("VA_test")) ? "0" : result.getString("VA_test") ) );
                      costInfo.setVaTR( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("VA_TR")) ? "0" : result.getString("VA_TR")) );
                      costInfo.setStdInAssy( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("StdIn_Assy")) ? "0" : result.getString("StdIn_Assy")) );
                      costInfo.setStdInTest( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("StdIn_Test")) ? "0" : result.getString("StdIn_Test")) );
                      costInfo.setStdInTR( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("StdIn_TR")) ? "0" : result.getString("StdIn_TR")) );
                      costInfo.setStdOutAssy( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("StdOut_assy")) ? "0" : result.getString("StdOut_assy")) );
                      costInfo.setStdOutTest( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("StdOut_Test")) ? "0" : result.getString("StdOut_Test")) );
                      costInfo.setStdOutTR( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("StdOut_TR")) ? "0" : result.getString("StdOut_TR")) );
                      costInfo.setYieldAssy( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("Yld_Assy")) ? "0" : result.getString("Yld_Assy")) );
                      costInfo.setYieldTest( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("Yld_Test")) ? "0" : result.getString("Yld_Test")) );
                      costInfo.setYieldTR( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("Yld_TR")) ? "0" : result.getString("Yld_TR")) );
                      costInfo.setStdNextQtr( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("Q+1_std")) ? "0" : result.getString("Q+1_std") ) );
                      costInfo.setStdNextQtrPlusOne( Double.parseDouble("#N/A!".equalsIgnoreCase(result.getString("Q+2_std")) ? "0" : result.getString("Q+2_std")) );

                      costList.add(costInfo);
                }
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw e;
        }

        return costList;
    }

    private void log( String message )
    {
        if ( message == null ) return;

        if ( m_parserLogger != null )
        {
            m_parserLogger.logDebug( message + LINE_SEPARATOR );
        }
        else
        {
            System.out.println( message );
        }
    } // end of log method
}

